package com.Aug.dao;

import com.Aug.entity.Dept;
import com.Aug.entity.User;
import com.sun.org.apache.xml.internal.utils.res.XResourceBundle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao extends BaseDao{

    //保存用户
    public int save(User user) {

        String sql = "insert into user(username,password,profile) values (?,?,?)";
        PreparedStatement stat = null;
        Connection conn = getConn();

        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1,user.getName());
            stat.setString(2,user.getPassword());
            stat.setString(3,user.getProfile());
            return stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,null);
        }
        return 0;

    }

    public List<User> findUserByname(String username) {

        String sql = "select id,username,password,profile,dept_id from user where username = ?";
        PreparedStatement stat = null;
        Connection conn = getConn();
        ResultSet resultSet = null;
        List<User> users = new ArrayList<>();
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1,username);
            resultSet = stat.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String username1 = resultSet.getString("username");
                String password = resultSet.getString("password");
                String profile = resultSet.getString("profile");

                User user = new User(id,username1, password, profile);

                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,resultSet);
        }
        return users;
    }

    //获取所有的用户列表
    public List<User> findAllUsers(Integer currentPage, Integer pageSize) {

        String sql = "select u.id,u.username,u.password,u.profile,u.dept_id, d.name from user u left join dept d on u.dept_id = d.id limit ?,?";
        PreparedStatement stat = null;
        Connection conn = getConn();
        ResultSet resultSet = null;
        List<User> users = new ArrayList<>();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1,(currentPage-1) * pageSize);
            stat.setInt(2, pageSize);
            resultSet = stat.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String profile = resultSet.getString("profile");
                String deptName = resultSet.getString("name");
                int deptId = resultSet.getInt("dept_id");

                User user = new User(id, username, password, profile,new Dept(deptId,deptName));

                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,resultSet);
        }
        return users;
    }

    public int deleteById(String id) {

        String sql = "delete from user where id = ?";
        PreparedStatement stat = null;
        Connection conn = getConn();

        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1,id);

            return stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,null);
        }
        return 0;


    }


    public User findUserById(Integer id) {

        String sql = "select id,username,password,profile,dept_id from user where id = ?";
        PreparedStatement stat = null;
        Connection conn = getConn();
        ResultSet resultSet = null;
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1,id);
            resultSet = stat.executeQuery();

            while (resultSet.next()) {
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String profile = resultSet.getString("profile");
                int deptId = resultSet.getInt("dept_id");

                User user = new User(id,username, password, profile);
                Dept dept = new Dept();
                dept.setId(deptId);
                user.setDept(dept);
                return user;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,resultSet);
        }
        return null;
    }

    public int update(User user) {

        String sql = "update user set username=?,profile=?,dept_id=? where id=?";
        PreparedStatement stat = null;
        Connection conn = getConn();

        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1,user.getName());
            stat.setString(2,user.getProfile());
            stat.setInt(3,user.getDept().getId());
            stat.setInt(4,user.getId());

            return stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,null);
        }
        return  0;

    }

    //查询用户总数
    public Integer getUserTotal() {

        String sql = "select count(*) total from user";
        PreparedStatement stat = null;
        Connection conn = getConn();
        ResultSet res = null;

        try {
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            res.next();
            return res.getInt("total");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn,stat,res);
        }
        return  0;

    }
}
